package com.example.mystore.mapper;

import com.example.mystore.model.ProductCategory;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Date;

@Mapper
public interface ProductCategoryMapper {

    @Select("SELECT * FROM type WHERE id = #{id}")
    @ResultMap("productCategoryResultMap")
    ProductCategory getProductCategoryById(@Param("id") Integer id);

    @Select("SELECT * FROM type")
    @Results(id = "productCategoryResultMap", value = {
            @Result(property = "parentId", column = "parent_id"),
            @Result(property = "sortOrder", column = "sort_order"),
            @Result(property = "created", column = "created"),
            @Result(property = "updated", column = "updated")
    })
    List<ProductCategory> getAllProductCategories();

    @Select({
            "WITH RECURSIVE TypeHierarchy AS (",
            "    SELECT id, parent_id AS parentId, name, sort_order AS sortOrder,",
            "           status, created, updated, level",
            "    FROM type",
            "    WHERE parent_id = #{parentId}",
            "    UNION ALL",
            "    SELECT t.id, t.parent_id AS parentId, t.name, t.sort_order AS sortOrder,",
            "           t.status, t.created, t.updated, t.level",
            "    FROM type t",
            "    INNER JOIN TypeHierarchy th ON t.parent_id = th.id",
            ")",
            "SELECT * FROM TypeHierarchy ORDER BY sortOrder"
    })
    @ResultMap("productCategoryResultMap")
    List<ProductCategory> findParamsByParentId(@Param("parentId") Integer parentId);

    @Insert("INSERT INTO type (name, parent_id, level, status, sort_order, created, updated) VALUES (#{name}, #{parentId}, #{level}, #{status}, #{sortOrder}, #{created}, #{updated})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insertProductCategory(ProductCategory productCategory);

    @Select("SELECT * FROM type WHERE id = #{id}")
    @ResultMap("productCategoryResultMap")
    ProductCategory selectById(@Param("id") Integer id);

    @Select("SELECT MAX(sort_order) FROM type WHERE parent_id = #{parentId}")
    Integer getMaxSortOrderByParentId(@Param("parentId") Integer parentId);

    @Update("UPDATE type SET name = #{name}, updated = #{updated} WHERE id = #{id}")
    void updateProductCategory(@Param("id") Integer id, @Param("name") String name, @Param("updated") Date updated);

    @Delete("DELETE FROM type WHERE id = #{id}")
    void deleteProductCategory(@Param("id") Integer id);

    @Select("SELECT COUNT(*) FROM type WHERE parent_id = #{parentId}")
    int countSubcategories(@Param("parentId") Integer parentId);

    @Select("SELECT COUNT(*) FROM commodity WHERE parts_id = #{id}")
    int countProductsByCategoryId(@Param("id") Integer id);

    @Select("SELECT COUNT(*) FROM type WHERE name = #{name}")
    int checkProductCategoryNameExists(@Param("name") String name);
}
